import openpyxl
import  random
import string
import pymysql
import pandas as pd
#创建excel表
workbook=openpyxl.Workbook("user.xlsx")
workbook.create_sheet(title="用户信息")
workbook.save("user.xlsx")

workbook=openpyxl.load_workbook("user.xlsx")
sheet=workbook["用户信息"]
#创建表头
columns=["id","idcard","username","realname","pwd","telphone",
         "email","age","sex","address","hiredate","sal","job","company"]
for column_num,colmn_title in enumerate(columns,1):
    sheet.cell(row=1,column=column_num,value=colmn_title)
#获取数据
def Data():
    return {
        'id': random.randint(1, 10000),
        'idcard': ''.join(random.choices(string.digits, k=18)),
        'username': ''.join(random.choices(string.ascii_lowercase, k=10)),
        'realname': ''.join(random.choices(string.ascii_uppercase + string.ascii_lowercase, k=10)),
        'pwd': ''.join(random.choices(string.ascii_letters + string.digits, k=12)),
        'telphone': ''.join(random.choices(string.digits, k=11)),
        'email': ''.join(random.choices(string.ascii_lowercase, k=10)) + '@example.com',
        'age': random.randint(18, 65),
        'sex': random.choice(['Male', 'Female']),
        'address': ''.join(random.choices(string.ascii_letters + string.digits, k=50)),
        'hiredate': f'{random.randint(2000, 2023)}-{random.randint(1, 12):02d}-{random.randint(1, 28):02d}',
        'sal': round(random.uniform(3000, 10000), 2),
        'job': ''.join(random.choices(string.ascii_lowercase, k=20)),
        'company': ''.join(random.choices(string.ascii_lowercase, k=30))
    }
for row in range(2,2002):
    data=Data()
    for column_num,column_title in enumerate(columns,1):
        sheet.cell(row=row,column=column_num,value=data[column_title])
workbook.save("user.xlsx")
#创建连接
db=pymysql.connect(
    host="localhost",
    port=3306,
    user="root",
    password="123456",
    database="jiangsu"
)
try:
    df = pd.read_excel('user.xlsx')
    with db.cursor() as cursor:
        for i,row in df.iterrows(): #使用pandas库处理数据
            sql="insert into user_info values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
            cursor.execute(sql,tuple(row))
        db.commit()
except Exception as e:
    print("操作失败:\n",e)
finally:
    db.close()


